select formmain_0043.ID,
main0013.field0002 SEEPCode,max(main0013.field0009) Initiator,max(main0013.field0008) InitiatorWWID,max(main0013.field0012) Applicant,
max(main0013.field0011) ApplicantWWID,max(main0013.field0027)  Department,
max(CONVERT(varchar(10), main0013.field0018, 20)) ActivityStart,
max(CONVERT(varchar(10), main0013.field0019, 20)) ActivityEnd,
max(main0013.field0016) Country,max(main0013.field0017) City ,max(main0013.field0004) 'WFSTatusName',max(main0013.field0005) 'PA_Code',
max(main0013.field0014) 'ActType',max(isnull(main0013.field0038,0)) IO,max(main0013.field0034) 'IONO',max(main0013.field0015) 'ActCategoryCHName',max(main0013.field0006) 'PA_ActName',
max(main0013.field0023) 'HcpBudget',
max(main0013.field0024) 'TotalBudget', isnull(meeting.hcpGO,0) hcpGO,isnull(meeting.internalNum,0) internalNum,isnull(hcp.hcpNum,0)hcpNum
from formmain_0217 main0013
LEFT JOIN (
SELECT
	MEET.SEEPCode,
	SUM (	CASE	WHEN MEET.AttendeesType = '强生内部'		OR MEET.AttendeesType = '其他' THEN		num	ELSE 0 END	) 'internalNum',
	SUM (	CASE	WHEN MEET.AttendeesType LIKE 'HCP/GO%' THEN		num	ELSE		0	END) 'hcpGO'
FROM(
		SELECT		main0014.field0015 AttendeesType,	main0014.field0002 SEEPCode,	COUNT (id) num
		FROM	formmain_0215 main0014
		GROUP BY	main0014.field0002,	main0014.field0015
	) MEET GROUP BY  MEET.SEEPCode) meeting on main0013.field0002=meeting.SEEPCode
LEFT JOIN (
	SELECT	m.SEEPCode,COUNT (hcpcode) hcpNum
	FROM(	SELECT	field0013 AS 'SEEPCode',	field0015 hcpcode
				FROM	formmain_0216
				GROUP BY	field0013,	field0015
		) m
	GROUP BY	m.SEEPCode
) hcp  on main0013.field0002=hcp.SEEPCode
LEFT JOIN formmain_0043 ON formmain_0043.field0074=main0013.field0034
WHERE formmain_0043.ID IS NULL
GROUP BY main0013.field0002,hcp.hcpNum,meeting.hcpGO,meeting.internalNum,formmain_0043.ID